package com.dp.service.impl3;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.SnAreaVo;
import com.dp.util.DataMap;
import com.dp.vo.NewsSnArea;
import com.dp.vo.NewsSnAreaPK;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class SnareaServiceImpl extends BaseImpl {

	/**
	 * Find all the NewsSnArea Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<SnAreaVo>
	 */
	public List<SnAreaVo> findAllSnAreaEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		String areaids=(String) hashMap.get("areaids");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<SnAreaVo> list=new ArrayList<SnAreaVo>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<NewsSnArea> queryList=new ArrayList<NewsSnArea>();
			queryList = query.getResultList();
			SnAreaVo snAreaVo = null;
			for(NewsSnArea snarea_temp:queryList){
				snAreaVo=new SnAreaVo();
				snAreaVo.setSn(snarea_temp.getNewsSnAreaPK().getSn());
				snAreaVo.setAreaid(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaid()+"");
				snAreaVo.setAreaname(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaname());
				snAreaVo.setArea_code(snarea_temp.getNewsSnAreaPK().getAreainfo().getArea_code());
				snAreaVo.setSoftagent(snarea_temp.getSoftagent());
				snAreaVo.setDevicetype(snarea_temp.getDevicetype());
				list.add(snAreaVo);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllSnAreaEntityCount(DataMap hashMap){
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		String areaids=(String) hashMap.get("areaids");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.newsSnAreaPK.sn) FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}

	public List<TAreainfo> findAllAreainfo(int upareaid) {
		List<TAreainfo> queryList=new ArrayList<TAreainfo>();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p WHERE p.upareaid=:upareaid ");
	    	sqlS.append(" order by p.areaid");
	        Query query = em.createQuery(sqlS.toString());      
	        query.setParameter("upareaid", upareaid);
	        queryList=query.getResultList();  
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return queryList;
	}

	/**
	 * 新增
	 * @param paramMap
	 * @return
	 */
	public Boolean addSnarea(DataMap paramMap) {
		List<String> snL=(List<String>) paramMap.get("snL");
		String area_id=(String) paramMap.get("area_id");
		NewsSnArea snarea=(NewsSnArea) paramMap.get("snarea");
		Boolean resultb=true;
//		try {
//			this.init();
//			for(String sn:snL){
//				this.sql="select sn from news_sn_area where sn=?";
//				prep=conn.prepareStatement(sql);
//				prep.setString(1, sn);
//				rs1=prep.executeQuery();
//				if(!rs1.next()){
//					this.sql="insert into news_sn_area (sn,areaname,softagent,device_type) values(?,?,?,?)";
//					prep=conn.prepareStatement(sql);
//					prep.setString(1, sn);
//					prep.setString(2, area_id);
//					prep.setString(3, snarea.getSoftagent());
//					prep.setInt(4, snarea.getDevicetype());
//					prep.executeUpdate();
//					//更新news_clientinfo中的区域
//					this.sql="update news_clientinfo set softversion=? where mencoding=?";
//					prep=conn.prepareStatement(sql);
//					prep.setString(1, area_id);
//					prep.setString(2, sn);
//					prep.executeUpdate();
//				}else{
//					if(snL.size()==1){
//						resultb=null;
//					}
//				}
//			}
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.error("addSnarea fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	/**
	 * 修改
	 * @param dataMap
	 * @return
	 */
	public Boolean updateSnarea(DataMap paramMap) {
		SnAreaVo snAreaVo_before=(SnAreaVo) paramMap.get("snAreaVo_before");
		SnAreaVo snAreaVo=(SnAreaVo) paramMap.get("snAreaVo");
		Boolean resultb=true;
//		try {
//			this.init();
//			this.sql="select sn,areaname from news_sn_area where sn=?";
//			prep=conn.prepareStatement(sql);
//			prep.setString(1, snAreaVo.getSn());
//			rs1=prep.executeQuery();
//			boolean updateD=false;
//			if(rs1.next()){
//				if(rs1.getString(1).equals(snAreaVo_before.getSn())&&rs1.getString(2).equals(snAreaVo_before.getAreaid())){
//					//自身
//					updateD=true;
//				}else{
//					resultb=null;
//				}
//			}else{
//				//全新
//				updateD=true;
//			}
//			if(updateD){
//				this.sql="update news_sn_area set sn=?,areaname=?,softagent=?,device_type=? where sn=? and areaname=?";
//				prep=conn.prepareStatement(sql);
//				prep.setString(1, snAreaVo.getSn());
//				prep.setString(2, snAreaVo.getAreaid());
//				prep.setString(3, snAreaVo.getSoftagent());
//				prep.setInt(4, snAreaVo.getDevicetype());
//				prep.setString(5, snAreaVo_before.getSn());
//				prep.setString(6, snAreaVo_before.getAreaid());
//				prep.executeUpdate();
//				
//				if(snAreaVo_before.getSn().equals(snAreaVo.getSn())&&!snAreaVo_before.getAreaid().equals(snAreaVo.getAreaid())){
//					//sn相同时，更改区域。则更新news_clientinfo中的区域
//					this.sql="update news_clientinfo set softversion=? where mencoding=?";
//					prep=conn.prepareStatement(sql);
//					prep.setString(1, snAreaVo.getAreaid());
//					prep.setString(2, snAreaVo.getSn());
//					prep.executeUpdate();
//				}
//				if(!snAreaVo_before.getSn().equals(snAreaVo.getSn())){
//					//sn不相同时
//					//更新的新的sn对应clientid在news_clientinfo中的区域
//					this.sql="update news_clientinfo set softversion=? where mencoding=?";
//					prep=conn.prepareStatement(sql);
//					prep.setString(1, snAreaVo.getAreaid());
//					prep.setString(2, snAreaVo.getSn());
//					prep.executeUpdate();
//					
//					//更新的老的sn对应clientid在news_clientinfo中的区域
//					this.sql="update news_clientinfo set softversion=? where mencoding=?";
//					prep=conn.prepareStatement(sql);
//					this.sql="select max(areaname) from news_sn_area where sn=?";
//					prep1=conn.prepareStatement(sql);
//					prep1.setString(1, snAreaVo_before.getSn());
//					rs1=prep1.executeQuery();
//					if(rs1.next()){
//						prep.setString(1, rs1.getString(1));
//					}else{
//						prep.setString(1, null);
//					}
//					prep.setString(2, snAreaVo_before.getSn());
//					prep.executeUpdate();
//				}
//				
//			}
//			
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.error("updateSnarea fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	/**
	 * 删除
	 * @param ids
	 * @return
	 */
	public boolean deleteSnarea(List<SnAreaVo> snvList) {
		boolean resultb=true;
//		try {
//			this.init();
//			this.sql="delete from news_sn_area where sn=? and areaname=?";
//			prep=conn.prepareStatement(sql);
//			for(SnAreaVo snAreaVo:snvList){
//				prep.setString(1, snAreaVo.getSn());
//				prep.setString(2, snAreaVo.getAreaid());
//				prep.addBatch();
//			}
//			prep.executeBatch();
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.error("deleteSnarea fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}

	
	public NewsSnArea findSnareaByid(NewsSnAreaPK snAreaPK) {
		NewsSnArea o = null;
		try {
			o = this.em.find(NewsSnArea.class, snAreaPK);
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return o;
	}
	
	
	/**
	 * 获得areaid对应区域信息
	 * 1.查看详情时使用
	 * @param areaid
	 * @return
	 */
	public SnAreaVo findAreaInfo2ByAreaid(String areaid) {
		SnAreaVo areainfo=new SnAreaVo();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	for(TAreainfo tai:queryList){
	        		if(tai.getAreaid().equals(Integer.parseInt(areaid))){
	        			List<TAreainfo> areainfo_templist=new ArrayList<TAreainfo>();
	    	        	areainfo_templist = getAreainfo_templist(queryList,tai,areainfo_templist);
	    	        	for(TAreainfo areainfo_temp :areainfo_templist){
	    	        		if(areainfo_temp.getArealevel()==3){
	    	        			areainfo.setArea_county(areainfo_temp.getAreaname()+"");
	    	        		}else if(areainfo_temp.getArealevel()==2){
	    	        			areainfo.setArea_city(areainfo_temp.getAreaname()+"");
	    	        		}else if(areainfo_temp.getArealevel()==1){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaname()+"");
	    	        		}else if(areainfo_temp.getArealevel()==0){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaname()+"");
	    	        		}
	    	        	}
	    	        	break;
	        		}
	        	}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areainfo;
	}
	
	/**
	 * 获得areaid对应区域信息
	 * 1.编辑时使用
	 * @param areaid
	 * @return
	 */
	public SnAreaVo findAreaInfoByAreaid(String areaid) {
		SnAreaVo areainfo=new SnAreaVo();
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	for(TAreainfo tai:queryList){
	        		if(tai.getAreaid().equals(Integer.parseInt(areaid))){
	        			List<TAreainfo> areainfo_templist=new ArrayList<TAreainfo>();
	    	        	areainfo_templist = getAreainfo_templist(queryList,tai,areainfo_templist);
	    	        	for(TAreainfo areainfo_temp :areainfo_templist){
	    	        		if(areainfo_temp.getArealevel()==3){
	    	        			areainfo.setArea_county(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==2){
	    	        			areainfo.setArea_city(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==1){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaid()+"");
	    	        		}else if(areainfo_temp.getArealevel()==0){
	    	        			areainfo.setArea_province(areainfo_temp.getAreaid()+"");
	    	        		}
	    	        	}
	    	        	break;
	        		}
	        	}
	        	if(areainfo.getArea_county()!=null){
	        		areainfo.setAreaid(areainfo.getArea_county());
        		}else if(areainfo.getArea_city()!=null){
	        		areainfo.setAreaid(areainfo.getArea_city());
        		}else if(areainfo.getArea_province()!=null){
	        		areainfo.setAreaid(areainfo.getArea_province());
        		}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areainfo;
	}
	/**
	 * 根据区域id递归得到上级对象
	 * @param tailist
	 * @param tai_temp
	 * @param areainfo_templist
	 * @return
	 */
	private List<TAreainfo> getAreainfo_templist(List<TAreainfo> tailist,TAreainfo tai_temp,List<TAreainfo> areainfo_templist){
		areainfo_templist.add(tai_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getAreaid()!=0&&tc3.getAreaid().equals(tai_temp.getUpareaid())){
				getAreainfo_templist(tailist,tc3,areainfo_templist);
			}
		}
		return areainfo_templist;
	}
	
	/**
	 * 预处理得到检索条件中所有的areaid
	 * @param area_id
	 * @return
	 */
	public String findAllAreaids(Integer area_id) {
		String areaids=null;
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	List<Integer> areaid_templist=new ArrayList<Integer>();
	        	areaid_templist = getAreaid_templist(queryList,area_id,areaid_templist);
	        	areaids="";
	        	for(Integer areaid_temp:areaid_templist){
	        		areaids+="'"+areaid_temp+"',";
	        	}
	        	if(!"".equals(areaids)){
	        		areaids=areaids.substring(0, areaids.length()-1);
	        	}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areaids;
	}

	private List<Integer> getAreaid_templist(List<TAreainfo> tailist,int areaid_temp,List<Integer> areaid_templist){
		areaid_templist.add(areaid_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getUpareaid().equals(areaid_temp)){
				if(tc3.getAreaid()!=0){
					getAreaid_templist(tailist,tc3.getAreaid(),areaid_templist);
				}
			}
		}
		return areaid_templist;
	}

	public List<String> findSoftagentL() {
		List<String> l=new ArrayList<String>();
		try {
		
				this.sql="select distinct magent from news_platforminfo";
				List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		    	for(Object[]o:tempp)
		    	{
					l.add(this.getString(o[0]));
				}
		
		} catch (Exception e) {
			logger.error("findSoftagentL fail !!!" + e.getMessage());
			e.printStackTrace();
		}
		return l;
	}

}
